<#assign object = doc.object>
package ${object.package}.service.base;
/**
* All the generated SQL statements for ${object.name} go in here.
* If you have custom queries, or you want to override existing ones, 
* best put them in here ${object.package}.service.${object.name}.
<#if object.trashcan == "true">
* <br>WARNING ${object.name} IMPLEMENTS TRASHCAN!!<br>
* Therefore, remember to suffix every where clause with 'AND roDeleted=false'.
</#if>
* @author Redora
* @see ${object.package}.service.${object.name}SQL
*/
public class ${object.name}SQLBase {

	/**
	 * Sequence of this model in the list of all models. This number is used
	 * to create an unique alias for select queries and is can also be used to 
	 * get the table name from ${object.package}.service.${object.name}Service.OBJECTS[].
	 * <br>
	 * Do not use the number hard coded, it can change when the model changes. Reference this
	 * or ALIAS instead.
	*/
	public final static int SEQUENCE = ${object.sequence};
	/** Table alias used for selet queries.*/
	public final static String ALIAS = "o${object.sequence}";

	/** The most important fields used for a tabular layout.*/
  	public final static String TABLE_FIELDS = ALIAS + ".id," +
		<#list object.tableScope?children as att>
		<#if att?node_name != "object">
    	ALIAS + ".${att.@fieldName}," +
		</#if>
	</#list>
    	ALIAS + ".creationDate," + ALIAS + ".updateDate";

  	/** All the fields except the related children.*/
  	public final static String FORM_FIELDS = ALIAS + ".id," +
	<#list object.formScope?children as att>
		<#if att?node_name != "object">
    	ALIAS + ".${att.@fieldName}," +
		</#if>
	</#list>
    	ALIAS + ".creationDate," + ALIAS + ".updateDate";

  	/** All the fields that are usually needed in the drop list as defined in the model.*/
  	public final static String LIST_FIELDS = ALIAS + ".id"
  	<#if object.listScope[0]??>
    <#list object.listScope?children as att>
    	<#if att?node_name != "object">
    	+ "," + ALIAS + ".${att.@fieldName}"
    	</#if>
    <#t>
    </#list>
    </#if>;
    
    /** All the fields that are lazy loading as defined in the model.*/
  	public final static String LAZY_FIELDS = ALIAS + ".id," +
  	<#if object.lazyScope[0]??>
    <#list object.lazyScope?children as att>
    	<#if att?node_name != "object">
    	ALIAS + ".${att.@fieldName}," +
    	</#if>
    <#t>
    </#list>
    </#if>
    	ALIAS + ".creationDate," + ALIAS + ".updateDate";

  	/** delete from ${object.name} where id = ? */
  	public final static String DELETE = "delete from ${object.name} where id = ?";
  	
  	/** default order for query */
  	public final static String DEFAULT_ORDER = " order by "
  		<#if doc["/object/tableScope/*[@sort]"][0]??>
  			<#list doc["/object/tableScope/*[@sort]"] as att>
  		+ "${att.@fieldName} <#if att.@sort != "1">desc</#if>";
  			<#break>
  			</#list>
  		<#else>
	  		<#if object.listScope[0]??>
	  			<#list object.listScope?children as att>
	  	+ "${att.@fieldName}" ;
	  			<#break>
	  			</#list>
	  		<#else>
	  			<#if doc["/object/tableScope/*[@notnull='true']"][0]??>
	  				<#list doc["/object/tableScope/*[@notnull='true']"] as att>
	  	+ "${att.@fieldName}" ;
	  				<#break>
	  				</#list>
	  			<#else>
	  				<#list doc["/object/tableScope/*"] as att>
	  	+ "${att.@fieldName}" ;
	  				<#break>
	  				</#list>
	  			</#if>
	  		</#if>
	  	</#if>

<#if object.trashcan == "true">
  	public final static String FIND_TRASH = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".roDeleted = true";
  	public final static String FIND_TRASH_BY_ID = 
  		"select " + FORM_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".roDeleted = true and " + ALIAS + ".id = ?";
  	public final static String TRASH = 
  		"update ${object.name} set roDeleted = ? where id = ?";

</#if>
  	public final static String FIND_BY_ID_FORM = 
  		"select " + FORM_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
  	public final static String FIND_BY_ID_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
	public final static String FIND_BY_ID_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
  		
  	public final static String FIND_ALL_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " <#if object.trashcan == "true">where " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;
  	public final static String FIND_ALL_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + " <#if object.trashcan == "true">where " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;

<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id"))>
  	public static final String FIND_BY_${att.@fieldName?upper_case}_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".${att.@fieldName} = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;
  	public static final String FIND_BY_${att.@fieldName?upper_case}_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".${att.@fieldName} = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;

  	public static final String DELETE_BY_${att.@fieldName?upper_case} = 
  		"delete from ${object.name} where ${att.@fieldName} = ?";

        <#if object.trashcan == "true">
  	public static final String TRASH_BY_${att.@fieldName?upper_case} = 
  		"update ${object.name} set roDeleted = ? where ${att.@fieldName} = ?";
        </#if>
    </#if>
</#list>

<#list object.attributes.enum as att>
  	public static final String FIND_BY_${att.@fieldName?upper_case}_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".${att.@fieldName} = ?<#if object.trashcan == "true"> and " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
  	public static final String FIND_BY_${att.@fieldName?upper_case}_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".${att.@fieldName} = ?<#if object.trashcan == "true"> and " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
</#list>

<#list doc["/object/attributes/*[@finder='true']"] as att>
	public static final String FIND_BY_${att.@fieldName?upper_case}_TABLE = 
		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + " where " + ALIAS + ".${att.@fieldName} = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
	public static final String FIND_BY_${att.@fieldName?upper_case}_LIST = 
		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + " where  " + ALIAS + ".${att.@fieldName} = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
</#list>

	public static final String FETCH_LAZY_BY_ID = 
		"select " + LAZY_FIELDS + " from ${object.name} as " + ALIAS + " where  " + ALIAS + ".id = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>";

  	/** Create table statement for MySQL */
  	public final static String CREATE_TABLE = "create table ${object.name} ("
    	+ "id bigint not null auto_increment, "
<#list object.formScope?children as att>
    <#if att?node_name != "object" && att?node_type == "element">
    	+ "${att.@fieldName} <#t>
        <#if att?node_name == "boolean">
    	tinyint(1) <#t>
        <#elseif att?node_name == "date">
    	date <#t>
        <#elseif att?node_name == "datetime">
    	datetime <#t>
        <#elseif att?node_name == "enum">
    	enum(<#t>
            <#list att.element as value>
      	'${value.@name}'<#if value_has_next>,</#if><#t>
            </#list>
    	) <#t>
        <#elseif att?node_name=="integer">
    	int <#t>
        <#elseif att?node_name=="long">
    	bigint <#t>
    	<#elseif att?node_name=="double">
    	double <#t>
        <#elseif att?node_name=="string">
            <#if att.@maxlength?number &gt; 16000000>
    	longtext <#t>
            <#elseif att.@maxlength?number &gt; 65000>
    	mediumtext <#t>
            <#else>
    	varchar(${att.@maxlength}) <#t>
            </#if>
        <#elseif att?node_name == "xml" || att?node_name == "html">
    	longtext <#t>
        </#if>
        <#if att.@notnull[0]?? && att.@notnull == "true">
    	not null <#t>
        </#if>
    	,"<#t>
    
    </#if>
</#list>
<#if object.trashcan == "true">
    	+ "roDeleted boolean not null default false,"
</#if>
    	+ "creationDate timestamp default CURRENT_TIMESTAMP, updateDate timestamp null,"
    	+ " primary key (id)"
<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id")) || att?node_name == "enum">
    	+ ", index ${object.name}_${att.@name[0]!att.@class?uncap_first} (${att.@name[0]!att.@class?uncap_first})"
    </#if>
</#list>
<#list doc["/object/attributes/*[@finder='true']"] as att>
    	+ ", index ${object.name}_${att.@fieldName} (${att.@fieldName})"
</#list>
    	+ ") default CHARSET=utf8";<#t>
	
	//TODO not needed when we have pagination 
  	public static final String TEST = FIND_ALL_TABLE + " limit 10";
	
<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
  	/** Create table of set statement for MySQL */
  	public final static String CREATE_${att.@relationTableName?upper_case}_TABLE = "create table ${att.@relationTableName} ("
    	+ "id bigint not null auto_increment,"
    	+ "${att.@myKey} bigint not null,"
    	+ "${att.@theirKey} bigint not null,"
<#if object.trashcan == "true">
		+ "roDeleted boolean not null default false,"
</#if>
    	+ "creationDate timestamp not null default CURRENT_TIMESTAMP,updateDate timestamp null,"
    	+ "primary key (id)"
    	+ ", index ${att.@relationTableName}_${att.@myKey} (${att.@myKey})"
    	+ ", index ${att.@relationTableName}_${att.@theirKey} (${att.@theirKey})"
    	+ ") default CHARSET=utf8";
  
  	public final static String DELETE_${att.@relationTableName?upper_case}_RELATION = 
  		"delete from ${att.@relationTableName} where ${att.@myKey} = ? and ${att.@theirKey} = ?";

<#if object.trashcan == "true">
  	public final static String TRASH_${att.@relationTableName?upper_case}_RELATION = 
  		"update ${att.@relationTableName} set roDeleted = true where ${att.@myKey} = ? and ${att.@theirKey} = ?";
</#if>
  
  	public final static String DELETE_${att.@relationTableName?upper_case}_RELATION_BY_${att.@myKey?upper_case} = 
  		"delete from ${att.@relationTableName} where ${att.@myKey} = ?";

<#if object.trashcan == "true">
  	public final static String TRASH_${att.@relationTableName?upper_case}_RELATION_BY_${att.@myKey?upper_case} = 
  		"update ${att.@relationTableName} set roDeleted = ? where ${att.@myKey} = ?";
</#if>
  
  	public final static String INSERT_${att.@relationTableName?upper_case}_RELATION = 
  		"insert into ${att.@relationTableName} (${att.@myKey},${att.@theirKey},creationDate) values (?,?,now())";
  
  	public final static String FIND_${object.name?upper_case}_BY_${att.@theirKey?upper_case}_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + ", ${att.@relationTableName} as r where " + ALIAS + ".id = r.${att.@myKey} and r.${att.@theirKey} = ?<#if object.trashcan == "true">  and " + ALIAS + ".roDeleted = false</#if>";
  	public final static String FIND_${object.name?upper_case}_BY_${att.@theirKey?upper_case}_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + ", ${att.@relationTableName} as r where " + ALIAS + ".id = r.${att.@myKey} and r.${att.@theirKey} = ?<#if object.trashcan == "true">  and " + ALIAS + ".roDeleted = false</#if>";
  		
  	<#if att.@class == object.name>
  	public final static String FIND_${object.name?upper_case}_BY_${att.@myKey?upper_case}_LIST = 
  		"select " + LIST_FIELDS + " from ${object.name} as " + ALIAS + ", ${att.@relationTableName} as r where " + ALIAS + ".id = r.${att.@theirKey} and r.${att.@myKey} = ?<#if object.trashcan == "true">  and " + ALIAS + ".roDeleted = false</#if>";
  	public final static String FIND_${object.name?upper_case}_BY_${att.@myKey?upper_case}_TABLE = 
  		"select " + TABLE_FIELDS + " from ${object.name} as " + ALIAS + ", ${att.@relationTableName} as r where " + ALIAS + ".id = r.${att.@theirKey} and r.${att.@myKey} = ?<#if object.trashcan == "true">  and " + ALIAS + ".roDeleted = false</#if>";
  	</#if>	
</#list>
<#list object.queries.query as att>
	public final static String ${att.@name} = 
		"${att?replace('${', '" + ')?replace('}', ' + "')}";
</#list>
}
